#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
高铁动车费用报销分析脚本

此脚本用于分析高铁和动车的差旅费报销情况，计算超标率，并生成分析报告。
"""

import pandas as pd
import numpy as np
from pathlib import Path

# 配置常量
DATA_DIR = Path("data")
OUTPUT_DIR = DATA_DIR / "alldata"  # 新增输出目录
TRAVEL_DETAIL_FILE = DATA_DIR / "差旅报销行程明细(商旅).xlsx"
TRANSPORT_FEE_FILE = DATA_DIR / "差旅费城市间交通费.xlsx"
OUTPUT_FILE = OUTPUT_DIR / "高铁动车费用超标分析结果.xlsx"
PRICE_STANDARDS_FILE = OUTPUT_DIR / "二等座票价标准.csv"

# 排除规则配置
EXCLUDED_COMBINATIONS = [
    ('高级管理人员', '动车D（一等座）'),
    ('高级管理人员', '动车D（二等座）'),
    ('高级管理人员', '动车D（一等卧）'),
    ('高级管理人员', '动车D（二等卧）'),
    ('高级管理人员', '动车D（软卧/软席）'),
    ('高级管理人员', '高铁G/C（一等座）'),
    ('高级管理人员', '高铁G/C（二等座）'),
    ('正副处级人员', '动车D（一等座）'),
    ('正副处级人员', '动车D（二等座）'),
    ('正副处级人员', '动车D（一等卧）'),
    ('正副处级人员', '动车D（二等卧）'),
    ('正副处级人员', '动车D（软卧/软席）'),
    ('正副处级人员', '高铁G/C（二等座）'),
    ('科级及以下人员', '动车D（二等座）'),
    ('科级及以下人员', '高铁G/C（二等座）'),
]

def clean_amount(amount):
    """
    清理并转换金额数据为浮点数
    
    Args:
        amount: 原始金额数据，可能是字符串、数字或NaN
        
    Returns:
        float: 清理后的金额数值
    """
    if pd.isna(amount):
        return 0.0
    if isinstance(amount, str):
        # 移除逗号和空格
        cleaned_amount = amount.replace(",", "").strip()
        try:
            return float(cleaned_amount)
        except ValueError:
            return 0.0
    return float(amount)

def ensure_column_types(df):
    """
    确保关键列的数据类型正确
    
    Args:
        df (pd.DataFrame): 输入数据框
        
    Returns:
        pd.DataFrame: 处理后的数据框
    """
    # 确保唯一标识和员工编号为字符串类型
    if '唯一标识' in df.columns:
        df['唯一标识'] = df['唯一标识'].astype(str)
    if '员工编号' in df.columns:
        df['员工编号'] = df['员工编号'].astype(str)
    return df

def create_route_price_standards(transport_data):
    """
    创建路线票价标准字典
    
    Args:
        transport_data (pd.DataFrame): 交通费用数据
        
    Returns:
        dict: 路线票价标准字典
    """
    # 筛选二等座记录
    second_class_mask = transport_data["交通工具名称"].str.contains(r'(高铁|动车)', na=False)
    second_class_data = transport_data[second_class_mask]

    # 计算每个路线的标准票价（使用众数）
    price_standards = (second_class_data
        .groupby(["起始地名称", "目的地名称"])["含税金额"]
        .agg(lambda x: x.mode().iloc[0])
        .reset_index()
        .rename(columns={"含税金额": "二等座票价标准"}))
    
    # 转换为字典格式
    route_price_dict = price_standards.set_index(
        ["起始地名称", "目的地名称"])["二等座票价标准"].to_dict()
    
    # 为所有路线设置默认值
    all_routes = set(zip(transport_data['起始地名称'], transport_data['目的地名称']))
    for route in all_routes:
        if route not in route_price_dict:
            route_price_dict[route] = 0
            
    return route_price_dict

def map_standard_price(row, price_standards):
    """
    映射标准票价到行程记录
    
    Args:
        row (pd.Series): 数据行
        price_standards (dict): 票价标准字典
        
    Returns:
        float: 对应的标准票价
    """
    route_key = (row["起始地名称"], row["目的地名称"])
    return price_standards.get(route_key, np.nan)

def calculate_excess_rates(df):
    """
    计算超标率
    
    Args:
        df (pd.DataFrame): 包含实际金额和标准票价的数据框
        
    Returns:
        pd.DataFrame: 添加了超标率的数据框
    """
    df['超出率'] = (
        (df['含税金额'] - df['二等座票价标准']) / df['二等座票价标准']
    ) * 100
    
    # 处理标准票价为0的情况
    df.loc[df['二等座票价标准'] == 0, '超出率'] = None
    
    # 格式化超标率
    df['超出率'] = df['超出率'].apply(
        lambda x: f"{x:.2f}%" if pd.notnull(x) else None
    )
    
    return df

def filter_excluded_records(df):
    """
    过滤需要排除的记录
    
    Args:
        df (pd.DataFrame): 原始数据框
        
    Returns:
        pd.DataFrame: 过滤后的数据框
    """
    exclusion_mask = ~df.apply(
        lambda row: (row['报销级别名称'], row['交通工具名称']) in EXCLUDED_COMBINATIONS,
        axis=1
    )
    return df[exclusion_mask]

def save_price_standards_to_csv(price_dict):
    """
    将票价标准字典保存为CSV文件
    
    Args:
        price_dict (dict): 路线票价标准字典
    """
    # 将字典转换为DataFrame
    standards_data = []
    for (start, end), price in price_dict.items():
        standards_data.append({
            "起始地名称": start,
            "目的地名称": end,
            "二等座票价标准": price
        })
    
    standards_df = pd.DataFrame(standards_data)
    
    # 保存为CSV文件
    standards_df.to_csv(PRICE_STANDARDS_FILE, index=False, encoding='utf-8-sig')
    print(f"\n票价标准已保存至: {PRICE_STANDARDS_FILE}")

def main(once = False):
    """主程序入口"""
    try:
        # 确保输出目录存在
        OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
        
        # 读取数据文件
        print("正在读取数据文件...")
        travel_details = pd.read_excel(TRAVEL_DETAIL_FILE, engine='openpyxl')
        transport_fees = pd.read_excel(TRANSPORT_FEE_FILE, engine='openpyxl')
        
        # 数据预处理
        print("正在处理数据...")
        travel_details = travel_details.rename(columns={"人员编号": "员工编号"})
        transport_fees.loc[:, "含税金额"] = transport_fees["含税金额"].apply(clean_amount)
        transport_fees = transport_fees[transport_fees["含税金额"] > 0]
        
        # 确保数据类型一致
        travel_details = ensure_column_types(travel_details)
        transport_fees = ensure_column_types(transport_fees)
        
        # 合并数据
        merged_data = pd.merge(
            transport_fees,
            travel_details[["唯一标识", "员工编号", "报销级别", "报销级别名称"]],
            on=["唯一标识", "员工编号"],
            how="left"
        ).drop_duplicates()
        
        # 创建票价标准
        route_price_standards = create_route_price_standards(transport_fees)
        
        # 保存票价标准到CSV文件
        save_price_standards_to_csv(route_price_standards)
        
        # 打印票价标准信息
        print("\n二等座票价标准示例：")
        for (start, end), price in list(route_price_standards.items())[:5]:
            print(f"{start} - {end}: 价格={price:.2f}")

        second_class_mask = merged_data["交通工具名称"].str.contains(r'(高铁|动车)', na=False)
        merged_data = merged_data[second_class_mask]

        # 应用票价标准
        merged_data["二等座票价标准"] = merged_data.apply(
            lambda row: map_standard_price(row, route_price_standards),
            axis=1
        )
        
        # 计算超标率
        merged_data = calculate_excess_rates(merged_data)
        
        # 过滤排除项
        filtered_data = filter_excluded_records(merged_data)
        if once:
            return filtered_data
        
        # 保存结果
        filtered_data.to_excel(OUTPUT_FILE, index=False, engine='openpyxl')
        print(f"\n分析完成，结果已保存至: {OUTPUT_FILE}")
        print(f"总记录数: {len(filtered_data)}")
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        raise

if __name__ == "__main__":
    main()